﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sistemaAcademicoFinanceiroEscolasRelatorioProfessores
{
    public partial class relatorioAulasProfessores : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!Page.IsPostBack)
            {

                lstprofessor.Items.Clear();

                SqlConnection sqlp = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlp.Open();

                string selectProfessores = "select * from professor";

                SqlCommand cmdProfessores = new SqlCommand(selectProfessores, sqlp);
                SqlDataReader drProfessores = cmdProfessores.ExecuteReader();

                ListItem pSelecioneTodos = new ListItem();
                pSelecioneTodos.Value = "0";
                pSelecioneTodos.Text = "Todos os professores";

                lstprofessor.Items.Add(pSelecioneTodos);

                while (drProfessores.Read())
                {

                    ListItem pSelecione = new ListItem();
                    pSelecione.Value = drProfessores["codigoProfessor"].ToString();
                    pSelecione.Text = drProfessores["nomeProfessor"].ToString();    //listagem dos professores do dropdownlist

                    lstprofessor.Items.Add(pSelecione);

                }



                sqlp.Close();

            }
                      

        }

        void gerarRelatorio()
        {
            string dataInicial = txtDataInicial.Text;
            string dataFinal = txtDataFinal.Text;

            TimeSpan dif = Convert.ToDateTime(dataFinal).Subtract(Convert.ToDateTime(dataInicial));
            

            if (dif.Days >= 0)
            {
                //conexao
                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                string selectProfessor = "";

                if (lstprofessor.SelectedValue == "0")
                {

                    selectProfessor =  " SELECT a.codigoProfessor, a.codigoAluno, a.dataAula as dataAula, a.horaInicialAula as horaInicialAula, ";
                    selectProfessor += " a.horaFinalAula as horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula as descricaoAssuntoAula, a.observacoesAula as observacoesAula, a.valorHora, ";

                    selectProfessor += " a.valorTotalSemDesconto as valorTotalSemDesconto, a.valorTotalComDesconto as valorTotalComDesconto, p.nomeProfessor as nomeProfessor, d.descricaoDisciplina as descricaoDisciplina, al.codigoAluno, al.nomeAluno as nomeAluno, a.aulaRealizada as aulaRealizada ";
                    selectProfessor += " FROM aula a , professor p, disciplina d, aluno al ";

                    selectProfessor += " WHERE ";
                    selectProfessor += " a.codigoProfessor = p.codigoProfessor and ";
                    selectProfessor += " a.codigoAluno = al.codigoAluno and ";
                    selectProfessor += " a.codigoDisciplina = d.codigoDisciplina and ";
                    selectProfessor += " convert(datetime,dataAula,103) ";
                    selectProfessor += " BETWEEN convert(datetime,'" + dataInicial + "',103) AND convert(datetime,'" + dataFinal + "',103) order by p.nomeProfessor ";

                }
                else
                {

                    selectProfessor =  " SELECT a.codigoProfessor, a.codigoAluno, a.dataAula as dataAula, a.horaInicialAula as horaInicialAula, a.horaFinalAula as horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula as descricaoAssuntoAula, a.observacoesAula as observacoesAula, a.valorHora as valorHora,  ";
                    selectProfessor += " a.valorTotalSemDesconto as valorTotalSemDesconto, a.valorTotalComDesconto as valorTotalComDesconto , p.nomeProfessor as nomeProfessor, d.descricaoDisciplina as descricaoDisciplina, al.codigoAluno, al.nomeAluno as nomeAluno,a.aulaRealizada as aulaRealizada ";
                    selectProfessor += " FROM aula a , professor p, disciplina d, aluno al ";

                    selectProfessor += " WHERE ";
                    selectProfessor += " a.codigoProfessor = p.codigoProfessor and ";
                    selectProfessor += " a.codigoAluno = al.codigoAluno and ";
                    selectProfessor += " a.codigoDisciplina = d.codigoDisciplina and ";
                    selectProfessor += " a.codigoProfessor = " + lstprofessor.SelectedValue + " and  ";

                    selectProfessor += " convert(datetime,dataAula,103) ";

                    selectProfessor += " BETWEEN convert(datetime,'" + dataInicial + "',103) AND convert(datetime,'" + dataFinal + "',103) order by p.nomeProfessor ";


 

                }

                SqlCommand cmdProfessor = new SqlCommand(selectProfessor, sqlc);
                SqlDataReader drProfessor = cmdProfessor.ExecuteReader();


                //essa string abaixo receberá o código html da tabela
                string relatorio = "<table width=100% border=1 cellpadding=5 cellspacing=0 bordercolor=#99CCFF>";
                relatorio += "<tr>";
                relatorio += "<th>Nome professor</th>";
                relatorio += "<th>Data</th>";
                relatorio += "<th>Hora Inicial</th>";
                relatorio += "<th>Hora Final</th>";
                relatorio += "<th>Disciplina</th>";
                relatorio += "<th>Assunto</th>";
                relatorio += "<th>Valor da Hora</th>";
                relatorio += "<th>Valor Sem Desconto</th>";
                relatorio += "<th>Valor Com Desconto</th>";
                relatorio += "<th>Observacoes</th>";
                relatorio += "<th>Aulas realizadas</th>";                
                relatorio += "</tr>";

                string codigoProfessor = "";
                string nomeProfessor = "";
                string dataAula = "";
                string horaInicialAula = "";
                string horaFinalAula = "";
                string descricaoDisciplina = "";
                string descricaoAssuntoAula = "";
                string valorHora = "";
                string valorSemDesconto = "";
                string valorComDesconto = "";
                string observacoesAula = "";
                bool aulaRealizada = false;


                if (drProfessor.HasRows)
                {
                    while (drProfessor.Read())
                    {
                        codigoProfessor = drProfessor["codigoProfessor"].ToString();
                        nomeProfessor = drProfessor["nomeProfessor"].ToString();

                        string selectAula = "";


                        dataAula = drProfessor["dataAula"].ToString();
                        descricaoDisciplina = drProfessor["descricaoDisciplina"].ToString();
                        horaInicialAula = drProfessor["horaInicialAula"].ToString();
                        horaFinalAula = drProfessor["horaFinalAula"].ToString();
                        descricaoAssuntoAula = drProfessor["descricaoAssuntoAula"].ToString();
                        observacoesAula = drProfessor["observacoesAula"].ToString();
                        valorHora = drProfessor["valorHora"].ToString();
                        valorSemDesconto = drProfessor["valorTotalSemDesconto"].ToString();
                        valorComDesconto = drProfessor["valorTotalComDesconto"].ToString();
                        aulaRealizada = Convert.ToBoolean(drProfessor["aulaRealizada"]);                  




                        //AQUI CONTEUDO DO RELATÓRIO//
                        relatorio += "<tr>";
                        relatorio += "<td>" + nomeProfessor + "</td>";
                        relatorio += "<td>" + dataAula + "</td>";
                        relatorio += "<td>" + horaInicialAula + "</td>";
                        relatorio += "<td>" + horaFinalAula + "</td>";
                        relatorio += "<td>" + descricaoDisciplina + "</td>";
                        relatorio += "<td>" + descricaoAssuntoAula + "</td>";
                        relatorio += "<td>" + valorHora + "</td>";
                        relatorio += "<td>" + valorSemDesconto + "</td>";
                        relatorio += "<td>" + valorComDesconto + "</td>";
                        relatorio += "<td>" + observacoesAula + "</td>";
                        //relatorio += "<td>" + aulaRealizada + "</td>";



                        if (aulaRealizada == true)
                        {
                            relatorio += "<td>Sim</td>";

                        }

                        else
                        {
                            relatorio += "<td>Não</td>";


                        }                       


                    }
                }

                drProfessor.Close();
                cmdProfessor.Dispose();

                relatorio += "</table>";
                Panel1.Controls.Add(new LiteralControl(relatorio));

                sqlc.Close();
                //fechar conexao
            }
            else {
                string literalcc = "<Script language=\"javascript\">function erro() {alert('A data final deve ser maior que a data inicial');}</script><script language=JavaScript>erro();</script>";
                Panel1.Controls.Add(new LiteralControl(literalcc));
            }
            
        }

        protected void btnGerar_Click(object sender, EventArgs e)
        {
            gerarRelatorio();
        }
    }
}
